﻿if exists (select * from sys.objects where object_id = OBJECT_ID(N'is_unknown') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function is_unknown
go

create function is_unknown(@value varchar(100)='')
returns bit
as
begin
	if @value=''or @value='未知' or @value='00'
		return 1
	return 0
end
go

if exists (select * from sys.objects where object_id = OBJECT_ID(N'tree_getlinknames') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function tree_getlinknames
go

create function tree_getlinknames(@treeno bigint)
returns varchar(MAX)
as
begin
	declare @names varchar(MAX)
	declare @links varchar(MAX)
	set @names = ''
	set @links=''
	select @names=@names+' '+isnull(link_name,'NVL'),@links=@links+' '+isnull(link_url,'NVL') 
	from tree_link
	where tree_no=@treeno
	
	return @names
end
go

if exists (select * from sys.objects where object_id = OBJECT_ID(N'tree_getlinkurls') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function tree_getlinkurls
go

create function tree_getlinkurls(@treeno bigint)
returns varchar(MAX)
as
begin
	declare @names varchar(MAX)
	declare @links varchar(MAX)
	set @names = ''
	set @links=''
	select @names=@names+' '+isnull(link_name,'NVL'),@links=@links+' '+isnull(link_url,'NVL') 
	from tree_link
	where tree_no=@treeno
	
	return @links
end
go

if OBJECT_ID('tree_search','P') is not null
drop proc tree_search
go

create procedure tree_search
@shuxing varchar(100) ='',
@yexu varchar(100) = '',
@yexing varchar(100)='',
@yeyuan varchar(100)='',
@yejian varchar(100)='',
@yeji varchar(100)='',
@huaxing varchar(100)='',
@guoshi varchar(100)='',
@shupi varchar(100)='',
@danfuye varchar(100)='',
@guoshiyanse varchar(100)='',
@huaqi varchar(100)='',
@gaodu varchar(100)='',
@shuganyanse varchar(100)=''
as
begin
	SET NOCOUNT ON
	declare @score float
	declare @total float
	declare @sql varchar(100)
	declare @t table(tree_no bigint primary key, score float)
	
	declare @concept_id bigint
	declare @tree_no bigint
	declare @shuxing1 varchar(20)
	declare @shuxing2 varchar(20)
	declare @yexu1 varchar(20)
	declare @yexu2 varchar(20)
	declare @yexing1 varchar(20)
	declare @yexing2 varchar(20)
	declare @yexing3 varchar(20)
	declare @yexing4 varchar(20)
	declare @yexing5 varchar(20)
	declare @yeyuan1 varchar(20)
	declare @yeyuan2 varchar(20)
	declare @yejian1 varchar(20)
	declare @yejian2 varchar(20)
	declare @yeji1 varchar(20)
	declare @yeji2 varchar(20)
	declare @yeji3 varchar(20)
	declare @yeji4 varchar(20)
	declare @yeji5 varchar(20)
	declare @huaxing1 varchar(20)
	declare @huaxing2 varchar(20)
	declare @guoshi1 varchar(20)
	declare @guoshi2 varchar(20)
	declare @shupi1 varchar(20)
	declare @shupi2 varchar(20)
	declare @danfuye1 varchar(20)
	declare @guoshiyanse1 varchar(60)
	declare @huaqi1 varchar(60)
	declare @gaodu1 varchar(60)
	declare @shuganyanse1 varchar(60)
	declare @shuxing3 varchar(20)
	
	set @total = 0
	if  dbo.is_unknown(@shuxing)=0
		set @total = @total+1
	if dbo.is_unknown(@yexu)=0
		set @total = @total+1
	if dbo.is_unknown(@yexing)=0
		set @total = @total+1
	if dbo.is_unknown(@yeyuan)=0
		set @total = @total+1
	if dbo.is_unknown(@yejian)=0
		set @total = @total+1
	if dbo.is_unknown(@yeji)=0
		set @total = @total+1
	if dbo.is_unknown(@huaxing)=0
		set @total = @total+1
	if dbo.is_unknown(@guoshi)=0
		set @total = @total+1
	if dbo.is_unknown(@shupi)=0
		set @total = @total+1
	if dbo.is_unknown(@danfuye)=0
		set @total = @total+1
	if dbo.is_unknown(@guoshiyanse)=0
		set @total = @total+0.3
	if dbo.is_unknown(@huaqi)=0
		set @total = @total+0.3
	if dbo.is_unknown(@gaodu)=0
		set @total = @total+0.3
	if dbo.is_unknown(@shuganyanse)=0
		set @total = @total+0.3
	declare @cur cursor 
	set @cur = cursor for select * from tree_concept
	open @cur
	
	fetch next from @cur into @concept_id, @tree_no,@shuxing1,@shuxing2,@yexu1,@yexu2,@yexing1,@yexing2,@yexing3,@yexing4,@yexing5,
@yeyuan1,@yeyuan2,@yejian1,@yejian2,@yeji1,@yeji2,@yeji3,@yeji4,@yeji5,
@huaxing1,@huaxing2,@guoshi1,@guoshi2,@shupi1,@shupi2,
@danfuye1,@guoshiyanse1,@huaqi1,@gaodu1,@shuganyanse1,@shuxing3
	while @@FETCH_STATUS = 0
	begin
		set @score = 0
		if  dbo.is_unknown(@shuxing)=0 and @shuxing in (@shuxing1,@shuxing2,@shuxing3,@shuxing3)
			set @score = @score+1
		if dbo.is_unknown(@yexu)=0 and @yexu  in (@yexu1,@yexu2)
			set @score = @score+1
		if dbo.is_unknown(@yexing)=0 and @yexing  in (@yexing1,@yexing2,@yexing3,@yexing4,@yexing5)
			set @score = @score+1
		if dbo.is_unknown(@yeyuan)=0 and @yeyuan  in (@yeyuan1,@yeyuan2)
			set @score = @score+1
		if dbo.is_unknown(@yejian)=0 and @yejian in (@yejian1,@yejian2)
			set @score = @score+1
		if dbo.is_unknown(@yeji)=0 and @yeji in (@yeji1,@yeji2,@yeji3,@yeji4,@yeji5)
			set @score = @score+1
		if dbo.is_unknown(@huaxing)=0 and @huaxing  in (@huaxing1,@huaxing2)
			set @score = @score+1
		if dbo.is_unknown(@guoshi)=0 and @guoshi in (@guoshi1,@guoshi2)
			set @score = @score+1
		if dbo.is_unknown(@shupi)=0 and @shupi in (@shupi1,@shupi2)
			set @score = @score+1
		if dbo.is_unknown(@danfuye)=0 and @danfuye=@danfuye1
			set @score = @score+1
--		if dbo.is_unknown(@guoshiyanse)=0 and CHARINDEx(@guoshiyanse,@guoshiyanse1)>0
--			set @score = @score+0.3
--		if dbo.is_unknown(@huaqi)=0 and CHARINDEx(@huaqi,@huaqi1)>0
--			set @score = @score+0.3
--		if dbo.is_unknown(@gaodu)=0 and CHARINDEx(@gaodu,@gaodu1)>0
--			set @score = @score+0.3
--		if dbo.is_unknown(@shuganyanse)=0 and CHARINDEx(@shuganyanse,@shuganyanse1)>0
--			set @score = @score+0.3

		if(@total = 0.0)
			set @score = 1.0
		else
			set @score = @score/@total

		if(@total=0.0 or (@score >= 1.0 and (not exists(select * from @t where tree_no=@tree_no))))
			insert into @t values(@tree_no,@score)
			
		fetch next from @cur into @concept_id, @tree_no,@shuxing1,@shuxing2,@yexu1,@yexu2,@yexing1,@yexing2,@yexing3,@yexing4,@yexing5,
@yeyuan1,@yeyuan2,@yejian1,@yejian2,@yeji1,@yeji2,@yeji3,@yeji4,@yeji5,
@huaxing1,@huaxing2,@guoshi1,@guoshi2,@shupi1,@shupi2,
@danfuye1,@guoshiyanse1,@huaqi1,@gaodu1,@shuganyanse1,@shuxing3
	end

	close @cur
	deallocate @cur
	SET NOCOUNT OFF
	
	select t.score score, tree.tree_no,tree.tree_name,tree.latin_name,tree.imagelink,tree.class_name,tree.conv_name,tree.description, dbo.tree_getlinknames(tree.tree_no) link_names,dbo.tree_getlinkurls(tree.tree_no) link_urls from tree, @t t where tree.tree_no=t.tree_no order by score DESC
end
go

--相关树 
if OBJECT_ID('tree_relevant','P') is not null
drop proc tree_relevant
go
create procedure tree_relevant
@tree_no int
as
begin
	select tree.tree_no,tree.tree_name,tree.latin_name,tree.imagelink,tree.class_name,tree.conv_name,tree.description, dbo.tree_getlinknames(tree.tree_no) link_names,dbo.tree_getlinkurls(tree.tree_no) link_urls
	from tree,(select distinct b.tree_no 
				from  tree_category a, tree_category b 
				where a.tree_no=@tree_no and 
				a.genus_id=b.genus_id and 
				a.tree_no<>b.tree_no) c
	where tree.tree_no=c.tree_no and tree.tree_no<>@tree_no
end
go

--相似树
if OBJECT_ID('tree_similar','P') is not null
drop proc tree_similar
go
create procedure tree_similar
@tree_no int
as
begin
--	select tree.* 
--	from tree,(select distinct b.tree_no 
--				from tree_concept a, tree_concept b
--				where a.tree_no<>b.tree_no and 
--				a.tree_no=@tree_no and 
--				(
--                --(b.shuxing1 in (a.shuxing1,a.shuxing2,a.shuxing3)) or 
--				--(b.shuxing2 in (a.shuxing1,a.shuxing2,a.shuxing3)) or 
--				--(b.yexu1  in (a.yexu1,a.yexu2))or 
--				--(b.yexu2  in (a.yexu1,a.yexu2))or 
--				(b.yexing1  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
--				(b.yexing2  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
--				(b.yexing3  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
--				(b.yexing4  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
--				(b.yexing5  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)))) c
--				--or 
--				--(b.yeyuan1  in (a.yeyuan1,a.yeyuan2)) or 
--				--(b.yeyuan2 in (a.yeyuan1,a.yeyuan2)) or 
--				--(b.yejian1 in (a.yejian1,a.yejian2)) or 
--				--(b.yejian2 in (a.yejian1,a.yejian2)) or 
--				--(b.yeji1 in (a.yeji1,a.yeji2,a.yeji3,a.yeji4,a.yeji5))or 
--				--(b.yeji2 in (a.yeji1,a.yeji2,a.yeji3,a.yeji4,a.yeji5)) or 
--				--(b.yeji3  in (a.yeji1,a.yeji2,a.yeji3,a.yeji4,a.yeji5))or 
--				--(b.yeji4  in (a.yeji1,a.yeji2,a.yeji3,a.yeji4,a.yeji5))or 
--				--(b.yeji5  in (a.yeji1,a.yeji2,a.yeji3,a.yeji4,a.yeji5))or 
--				--(b.huaxing1  in (a.huaxing1,a.huaxing1)) or 
--				--(b.huaxing2 in (a.huaxing1,a.huaxing2)) or 
--				--(b.guoshi1 in (a.guoshi1,a.guoshi2)) or 
--				--(b.guoshi2 in (a.guoshi1,a.guoshi2))  or 
--				--(b.shupi1 in (a.shupi1,a.shupi2))  or 
--				--(b.shupi2 in (a.shupi1,a.shupi2)) or 
--				--(b.danfuye=a.danfuye))

	select tree.tree_no,tree.tree_name,tree.latin_name,tree.imagelink,tree.class_name,tree.conv_name,tree.description, dbo.tree_getlinknames(tree.tree_no) link_names,dbo.tree_getlinkurls(tree.tree_no) link_urls
	from tree,(select distinct b.tree_no 
				from tree_concept a, tree_concept b
				where a.tree_no=@tree_no and a.tree_no<>b.tree_no and 
				((b.yexing1  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
				(b.yexing2  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
				(b.yexing3  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
				(b.yexing4  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)) or 
				(b.yexing5  in (a.yexing1,a.yexing2,a.yexing3,a.yexing4,a.yexing5)))) c
	where tree.tree_no=c.tree_no and tree.tree_no<>@tree_no
end
go

if OBJECT_ID('tree_getinfo','P') is not null
drop proc tree_getinfo
go
create procedure tree_getinfo
@tree_no int
as
begin
	select 
	tree.tree_no,tree.tree_name,tree.latin_name,tree.imagelink,tree.class_name,tree.conv_name,tree.description, dbo.tree_getlinknames(tree.tree_no) link_names,dbo.tree_getlinkurls(tree.tree_no) link_urls,
	tree_category.category_id,tree_category.class_id,tree_category.family_id,tree_category.genus_id,tree_category.phylum_id,tree_category.sibling_tree_name,tree_category.symbiosis,
	tree_use.*
	from tree left join tree_category on tree.tree_no=tree_category.tree_no
	left join tree_use on tree.tree_no=tree_use.tree_no
	where tree.tree_no=@tree_no
end
go

if OBJECT_ID('tree_search_bycategory','P') is not null
drop proc tree_search_bycategory
go

create procedure tree_search_bycategory
@phylum varchar(100) ='',
@classid varchar(100) = '',
@family varchar(100)='',
@symbiosis varchar(100)=''
as
begin
	if @phylum='' and @classid='' and @family='' and @symbiosis=''
	begin
		select tree.tree_no,tree.tree_name,tree.latin_name,tree.imagelink,tree.class_name,tree.conv_name,tree.description, dbo.tree_getlinknames(tree.tree_no) link_names,dbo.tree_getlinkurls(tree.tree_no) link_urls from tree
	end	
    else
		begin
			select tree.tree_no,tree.tree_name,tree.latin_name,tree.imagelink,tree.class_name,tree.conv_name,tree.description, dbo.tree_getlinknames(tree.tree_no) link_names,dbo.tree_getlinkurls(tree.tree_no) link_urls
			from tree,tree_category 
			where tree.tree_no=tree_category.tree_no and
				(tree_category.phylum_id=@phylum or @phylum='') and 
				(tree_category.class_id=@classid or @classid='') and 
				(tree_category.family_id=@family or @family='') and 
				(tree_category.genus_id=@symbiosis or @symbiosis='');
		end
end
go

if OBJECT_ID('tree_add','P') is not null
drop proc tree_add
go

create procedure tree_add
	@tree_name varchar(60),	--树名称(学名)
	@latin_name	varchar(60),--拉丁名
	@class_name_1 varchar(60),--统称
	@class_name_2 varchar(60),--统称
	@class_name_3 varchar(60),--统称
	@conv_name_1	varchar(60),--俗名
	@conv_name_2	varchar(60),--俗名
	@conv_name_3	varchar(60),--俗名
	@conv_name_4	varchar(60),--俗名
	@conv_name_5	varchar(60),--俗名
	@conv_name_6	varchar(60),--俗名
	@conv_name_7	varchar(60),--俗名
	@conv_name_8	varchar(60),--俗名
	@conv_name_9	varchar(60),--俗名
	@conv_name_10	varchar(60),--俗名
	@conv_name_11	varchar(60),--俗名
	@conv_name_12	varchar(60),--俗名
	@conv_name_13	varchar(60),--俗名
	@conv_name_14	varchar(60),--俗名
	@conv_name_15	varchar(60),--俗名,
	@description varchar(MAX),	--描述,
	@imagelink varchar(60)
as
begin
	insert into tree(tree_name,latin_name,class_name_1,class_name_2,class_name_3,conv_name_1,conv_name_2,conv_name_3,conv_name_4,conv_name_5,conv_name_6,conv_name_7,conv_name_8,conv_name_9,conv_name_10,conv_name_11,conv_name_12,conv_name_13,conv_name_14,conv_name_15,description,imagelink)
	values(@tree_name,@latin_name,@class_name_1,@class_name_2,@class_name_3,@conv_name_1,@conv_name_2,@conv_name_3,@conv_name_4,@conv_name_5,@conv_name_6,@conv_name_7,@conv_name_8,@conv_name_9,@conv_name_10,@conv_name_11,@conv_name_12,@conv_name_13,@conv_name_14,@conv_name_15,@description,@imagelink)
end
go

if OBJECT_ID('tree_delete','P') is not null
drop proc tree_delete
go

create procedure tree_delete
	@tree_no int 
as
begin
	begin tran
		delete from tree_derive_concept where derive_no in (select distinct parent_tree_no from tree_derive where derive_no=@tree_no)
		delete from tree_derive where parent_tree_no=@tree_no
		delete from tree_concept where tree_no=@tree_no
		delete from tree_category where tree_no=@tree_no
		delete from tree_use where tree_no=@tree_no
		delete from tree_link where tree_no=@tree_no
		delete from tree where tree_no=@tree_no
	commit tran
end
go